<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="aj20941">ALTER DOMAIN</title><body><p id="sql_command_desc">Changes the definition of a domain.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">ALTER DOMAIN <varname>name</varname> { SET DEFAULT <varname>expression</varname> | DROP DEFAULT }

ALTER DOMAIN <varname>name</varname> { SET | DROP } NOT NULL

ALTER DOMAIN <varname>name</varname> ADD <varname>domain_constraint</varname> [ NOT VALID ]

ALTER DOMAIN <varname>name</varname> DROP CONSTRAINT [ IF EXISTS ] <varname>constraint_name</varname> [RESTRICT | CASCADE]

ALTER DOMAIN <varname>name</varname> RENAME CONSTRAINT <varname>constraint_name</varname> TO <varname>new_constraint_name</varname>

ALTER DOMAIN <varname>name</varname> VALIDATE CONSTRAINT <varname>constraint_name</varname>
  
ALTER DOMAIN <varname>name</varname> OWNER TO <varname>new_owner</varname>
  
ALTER DOMAIN <varname>name</varname> RENAME TO <varname>new_name</varname>

ALTER DOMAIN <varname>name</varname> SET SCHEMA <varname>new_schema</varname></codeblock></section><section id="section3"><title>Description</title><p><codeph>ALTER DOMAIN</codeph> changes the definition of an existing
domain. There are several sub-forms: </p><ul><li id="aj136679"><b>SET/DROP DEFAULT</b> — These forms set or remove the default value
for a domain. Note that defaults only apply to subsequent <codeph>INSERT</codeph>
commands. They do not affect rows already in a table using the domain.
</li><li id="aj136731"><b>SET/DROP NOT NULL</b> — These forms change whether a domain is
marked to allow <codeph>NULL</codeph> values or to reject <codeph>NULL</codeph>
values. You may only <codeph>SET NOT NULL</codeph> when the columns using
the domain contain no null values.</li>
        <li id="aj136747"><b>ADD <varname>domain_constraint</varname> [ NOT VALID ]</b> — This form
          adds a new constraint to a domain using the same syntax as <codeph>CREATE DOMAIN</codeph>.
          When a new constraint is added to a domain, all columns using that domain will be checked
          against the newly added constraint. These checks can be suppressed by adding the new
          constraint using the <codeph>NOT VALID</codeph> option; the constraint can later be made
          valid using <codeph>ALTER DOMAIN ... VALIDATE CONSTRAINT</codeph>. Newly inserted or
          updated rows are always checked against all constraints, even those marked <codeph>NOT
            VALID</codeph>. <codeph>NOT VALID</codeph> is only accepted for <codeph>CHECK</codeph>
          constraints. </li>
        <li id="aj136757"><b>DROP CONSTRAINT [ IF EXISTS ]</b> — This form drops constraints on a
          domain. If <codeph>IF EXISTS</codeph> is specified and the constraint does not exist, no
          error is thrown. In this case a notice is issued instead. </li>
        <li><b>RENAME CONSTRAINT</b> — This form changes the name of a constraint on a domain.</li>
        <li><b>VALIDATE CONSTRAINT</b> — This form validates a constraint previously added as
            <codeph>NOT VALID</codeph>, that is, verify that all data in columns using the domain
          satisfy the specified constraint.</li>
  <li id="aj136764"><b>OWNER</b> — This form changes the owner of the domain to the specified
user. </li>
        <li><b>RENAME</b> — This form changes the name of the domain. </li>
  <li id="aj136771"><b>SET SCHEMA</b> — This form changes the schema of the domain. Any
constraints associated with the domain are moved into the new schema
as well. </li></ul><p>You must own the domain to use <codeph>ALTER DOMAIN</codeph>. To change
the schema of a domain, you must also have <codeph>CREATE</codeph> privilege
on the new schema. To alter the owner, you must also be a direct or indirect
member of the new owning role, and that role must have <codeph>CREATE</codeph>
privilege on the domain's schema. (These restrictions enforce that
altering the owner does not do anything you could not do by dropping
and recreating the domain. However, a superuser can alter ownership of
any domain anyway.)</p></section><section id="section4"><title>Parameters</title><parml><plentry><pt><varname>name</varname></pt><pd>The name (optionally schema-qualified) of an existing domain to alter.
</pd></plentry><plentry><pt><varname>domain_constraint</varname></pt><pd>New domain constraint for the domain. </pd></plentry><plentry><pt><varname>constraint_name</varname></pt><pd>Name of an existing constraint to drop or rename. </pd></plentry>
        <plentry>
          <pt>NOT VALID</pt>
          <pd>Do not verify existing column data for constraint validity.</pd>
        </plentry><plentry><pt>CASCADE</pt><pd>Automatically drop objects that depend on the constraint. </pd></plentry><plentry><pt>RESTRICT</pt><pd>Refuse to drop the constraint if there are any dependent objects.
This is the default behavior. </pd></plentry>
        <plentry>
          <pt><varname>new_name</varname></pt>
          <pd>The new name for the domain.</pd>
        </plentry>
        <plentry>
          <pt><varname>new_constraint_name</varname></pt>
          <pd>The new name for the constraint.</pd>
        </plentry><plentry><pt><varname>new_owner</varname></pt><pd>The user name of the new owner of the domain. </pd></plentry><plentry><pt><varname>new_schema</varname></pt><pd>The new schema for the domain. </pd></plentry></parml></section><section id="section5"><title>Examples</title><p>To add a <codeph>NOT NULL</codeph> constraint to a domain: </p>
          <codeblock>ALTER DOMAIN zipcode SET NOT NULL;</codeblock>
          <p>To remove a <codeph>NOT NULL</codeph> constraint from a domain: </p>
          <codeblock>ALTER DOMAIN zipcode DROP NOT NULL;</codeblock>
          <p>To add a check constraint to a domain: </p>
          <codeblock>ALTER DOMAIN zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5);</codeblock>
          <p>To remove a check constraint from a domain: </p>
          <codeblock>ALTER DOMAIN zipcode DROP CONSTRAINT zipchk;</codeblock>
          <p>To rename a check constraint on a domain: </p>
          <codeblock>ALTER DOMAIN zipcode RENAME CONSTRAINT zipchk TO zip_check;</codeblock>
          <p>To move the domain into a different schema:</p>
          <codeblock>ALTER DOMAIN zipcode SET SCHEMA customers;</codeblock></section><section id="section6"><title>Compatibility</title>
      <p><codeph>ALTER DOMAIN</codeph> conforms to the SQL standard, except for the
          <codeph>OWNER</codeph>, <codeph>RENAME</codeph>, <codeph>SET SCHEMA</codeph>, and
          <codeph>VALIDATE CONSTRAINT</codeph> variants, which are Greenplum Database extensions.
        The <codeph>NOT VALID</codeph> clause of the <codeph>ADD CONSTRAINT</codeph> variant is also
        a Greenplum Database extension. </p></section><section id="section7"><title>See Also</title><p><codeph><xref href="./CREATE_DOMAIN.xml#topic1" type="topic" format="dita"/></codeph>,
            <codeph><xref href="./DROP_DOMAIN.xml#topic1" type="topic" format="dita"/></codeph></p></section></body></topic>
